//
//  SqliteTool.m
//  MeiTuan
//
//  Created by 何凯楠 on 16/6/20.
//  Copyright © 2016年 HeXiaoBa. All rights reserved.
//

#import "XTSqliteUtils.h"
#import "FMDB.h"

@implementation XTSqliteUtils

static FMDatabase *_db;
+ (void)initialize {
    
    NSString *cachePath =  NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)[0];
    
    NSString *filePath = [cachePath stringByAppendingPathComponent:@"data.sqlite"];
    
    _db = [FMDatabase databaseWithPath:filePath];
    
    if ([_db open]) {
        printf("打开数据库成功\n");
    } else {
        printf("打开数据库失败\n");
    }
    
    BOOL flag = [_db executeUpdate:@"create table if not exists t_user (id integer primary key autoincrement, username text, password text, score double, type integer);"];
    if (flag) {
        printf("创建表成功\n");
    } else {
        printf("创建表失败\n");
    }

}

+ (BOOL)insertUserWithUsename:(NSString *)username password:(NSString *)password type:(NSInteger)type {

    BOOL flag = [_db executeUpdate:@"insert into t_user (username, password, type) values (?, ?, ?);", username, password, @(type)];
    if (flag) {
        printf("插入数据成功\n");
    } else {
        printf("插入数据失败\n");
    }
    return flag;
}

+ (BOOL)deleteUserWithUsename:(NSString *)username {
    
    BOOL flag = [_db executeUpdate:@"delete from t_user where username = ?;", username];

    if (flag) {
        printf("删除成功\n");
    } else {
        printf("删除失败\n");
    }
    return flag;
}


+ (NSDictionary *)loginWithUsername:(NSString *)username password:(NSString *)password {
    
    NSMutableDictionary *dic = [NSMutableDictionary dictionary];
    BOOL result = NO;
    FMResultSet *rs = [_db executeQuery:@"select * from t_user where username = ?;", username];
    while ([rs next]) {
        
        NSString *db_password = [rs stringForColumn:@"password"];
        int db_type = [rs intForColumn:@"type"];
        if ([password isEqualToString:db_password]) {
            result = YES;
            dic[@"type"] = @(db_type);
        }
    }
    dic[@"status"] = @(result);
    return dic;
}

+ (NSArray *)queryStudents {
    
    NSMutableArray *arr = [NSMutableArray array];
    
    FMResultSet *rs = [_db executeQuery:@"select * from t_user where type = 0;"];
    while ([rs next]) {
        
        NSString *username = [rs stringForColumn:@"username"];
        NSString *password = [rs stringForColumn:@"password"];
        int type = [rs intForColumn:@"type"];
        double score = [rs doubleForColumn:@"score"];

        NSMutableDictionary *dic = [NSMutableDictionary dictionary];
        dic[@"username"] = username;
        dic[@"password"] = password;
        dic[@"score"] = @(score);
        dic[@"type"] = @(type);
        
        if (dic) {
            [arr addObject:dic];
        }
    }
    
    return [arr copy];
}

+ (BOOL)updateSocre:(double)score username:(NSString *)username {
    
    BOOL flag = [_db executeUpdate:@"update t_user set score = ? where username = ?;", @(score), username];
    
    if (flag) {
        printf("更新分数成功\n");
    } else {
        printf("更新分数失败\n");
    }
    return flag;
}


@end
